﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using ORS.DAL;

namespace ORS.BLL 
{
    public class GetPaperInfomation
    {
        string sqlstr_paper;
        string sqlstr_user;
        string sqlstr_interest;
        DataTable d_table, d_interestFLD;
        static mssqlopr ivk_mssqlopr;
        string user_id;

        static  GetPaperInfomation()
        {
            ivk_mssqlopr = new mssqlopr();
        }
        
            
            
        protected int m_paperid = 0;
        public int PAPER_ID
        {
            set { m_paperid = value; }
            get { return m_paperid; }
        }

        protected string m_title = "";
        public string TITLE
        {
            set { m_title = value.Trim(); }
            get { return m_title; }
        }

        protected string m_author = "";
        public string AUTHOR
        {
            set { m_author = value.Trim(); }
            get { return m_author; }
        }

        protected string m_reviewer = "";
        public string REVIEWER
        {
            set { m_reviewer = value.Trim(); }
            get { return m_reviewer; }
        }

        protected int m_interestid = 0;
        public int INTEREST_ID
        {
            set { m_interestid = value; }
            get { return m_interestid; }
        }

        public string interest_field = "";
        public string INTEREST
        {
            set { interest_field = value; }
            get { return interest_field; }
        }

        protected string m_description = "";
        public string DESCRIPTION
        {
            set { m_description = value; }
            get { return m_description; }
        }

        protected string m_contentlink = "";
        public string CONTENTLINK
        {
            set { m_contentlink = value.Trim(); }
            get { return m_contentlink; }
        }

        protected DateTime m_submitdate = new DateTime(2011, 3, 1);
        public DateTime SUBMITDATE
        {
            set { m_submitdate = value; }
            get { return m_submitdate; }
        }

        protected int m_reviewstate = 0;
        public int REVIEWSTATE
        {
            set { m_reviewstate = value; }
            get { return m_reviewstate; }
        }

        protected int m_approvestate = 0;
        public int APPROVESTATE
        {
            set { m_approvestate = value; }
            get { return m_approvestate; }
        }


        public DataTable GetPaperInfoList()
        {
            sqlstr_paper = "SELECT a.*,b.cname FROM tb_paperinfo a left join tb_userlist b ON a.authorid=b.userid WHERE a.approvestate=1";
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }
        public DataTable GetPaperInfoListtoDistribute()
        {
            sqlstr_paper = "SELECT a.*,b.cname FROM tb_paperinfo a left join tb_userlist b ON a.authorid=b.userid WHERE a.approvestate=0 and a.reviewstate=0";
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }

        public DataTable GetPaperInfolist_by_PaperID(string i_paper_id)
        {
            sqlstr_paper = "SELECT  tb_paperinfo.contentlink, tb_paperinfo.tiltle, tb_userlist.cname, tb_paperinfo.reviewer, tb_paperinfo.submitdate,  tb_revstades.rev_description, tb_apprstades.apr_description FROM  dbo.tb_paperinfo INNER JOIN tb_revstades ON tb_paperinfo.reviewstate = tb_revstades.reviewstate INNER JOIN tb_apprstades ON tb_paperinfo.approvestate = tb_apprstades.approvestate INNER JOIN tb_userlist ON tb_paperinfo.authorid = tb_userlist.userid WHERE tb_paperinfo.paperid like '%" + i_paper_id + "%'";
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }

        public DataTable GetPaperInfolist_by_title(string s_paper_title)
        {
            sqlstr_paper = "SELECT  tb_paperinfo.contentlink, tb_paperinfo.tiltle, tb_userlist.cname, tb_paperinfo.reviewer, tb_paperinfo.submitdate,  tb_revstades.rev_description, tb_apprstades.apr_description FROM  dbo.tb_paperinfo INNER JOIN tb_revstades ON tb_paperinfo.reviewstate = tb_revstades.reviewstate INNER JOIN tb_apprstades ON tb_paperinfo.approvestate = tb_apprstades.approvestate INNER JOIN tb_userlist ON tb_paperinfo.authorid = tb_userlist.userid WHERE tb_paperinfo.tiltle like '%" + s_paper_title + "%'";
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }

        public DataTable GetPaperInfolist_by_authorname(string s_author_name)
        {
            sqlstr_user = " SELECT * FROM tb_userlist WHERE cname='" + s_author_name + "'";
            user_id = ivk_mssqlopr.get_userid(sqlstr_user);
            sqlstr_paper = "SELECT  tb_paperinfo.contentlink, tb_paperinfo.tiltle, tb_userlist.cname, tb_paperinfo.reviewer, tb_paperinfo.submitdate,  tb_revstades.rev_description, tb_apprstades.apr_description FROM  dbo.tb_paperinfo INNER JOIN tb_revstades ON tb_paperinfo.reviewstate = tb_revstades.reviewstate INNER JOIN tb_apprstades ON tb_paperinfo.approvestate = tb_apprstades.approvestate INNER JOIN tb_userlist ON tb_paperinfo.authorid = tb_userlist.userid  WHERE tb_paperinfo.authorid like '%" + user_id + "%'";
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }
        public DataTable GetPaperInfolist_by_interest(string s_interest_id)
        {
            sqlstr_paper = "SELECT  tb_paperinfo.contentlink, tb_paperinfo.tiltle, tb_userlist.cname, tb_paperinfo.reviewer, tb_paperinfo.submitdate,  tb_revstades.rev_description, tb_apprstades.apr_description FROM  dbo.tb_paperinfo INNER JOIN tb_revstades ON tb_paperinfo.reviewstate = tb_revstades.reviewstate INNER JOIN tb_apprstades ON tb_paperinfo.approvestate = tb_apprstades.approvestate INNER JOIN tb_userlist ON tb_paperinfo.authorid = tb_userlist.userid WHERE tb_paperinfo.interestid=" + s_interest_id;
            return ivk_mssqlopr.GetDataTable(sqlstr_paper);
        }
    
        public void GetPaperInfo_by_PaperID(string s_paper_id)
        {
            sqlstr_paper = "SELECT a.*,b.cname FROM tb_paperinfo a left join tb_userlist b ON a.authorid=b.userid WHERE a.paperid='" + s_paper_id + "'";
            d_table = ivk_mssqlopr.GetDataTable(sqlstr_paper);
            m_paperid = (int)d_table.Rows[0]["paperid"];
            m_title = (string)d_table.Rows[0]["tiltle"];
            m_author = (string)d_table.Rows[0]["cname"];
            m_contentlink = (string)d_table.Rows[0]["contentlink"];
            m_reviewstate = (int)d_table.Rows[0]["reviewstate"];
            m_reviewer = (string)d_table.Rows[0]["reviewer"];
            m_interestid = (int)d_table.Rows[0]["interestid"];
            m_description = (string)d_table.Rows[0]["description"];
            sqlstr_interest = "SELECT * FROM tb_interest WHERE interestid=" + m_interestid;
            d_interestFLD = ivk_mssqlopr.GetDataTable(sqlstr_interest);
            interest_field = (string)d_interestFLD.Rows[0]["name"];
        }
    }
}